package db.newversion;

import java.awt.Color;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Observable;

import javax.swing.JOptionPane;

import com.sun.org.apache.xpath.internal.functions.WrongNumberArgsException;

import core.Constants;

public class ColorDB extends Observable implements IDataBase < Combination, Color > {
	private static ColorDB instance = null;
	private HashMap < Combination, Color > buffer;

	public static ColorDB getInstance() {
		if (instance == null) {
			instance = new ColorDB();
		}
		return instance;
	}

	private ColorDB() {
		try {
			this.createTable();
		} catch (SQLException e1) {

		}
		try {
			this.importAllColors();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "An error occurred when importing all colors from DB", "Error", JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}
	}

	/**
	 * @param toInsert
	 *            the Color to insert into the DB
	 * @param objects
	 *            [0] the bow cathegory [1] the age class [3] the gender
	 */
	@Override
	public boolean insert(Color toInsert, Object... objects) throws SQLException {
		Combination combination = new Combination(objects [0].toString(), objects [1].toString(), objects [2].toString());

		if (this.buffer.containsKey(combination)) {
			this.update(toInsert, objects);
		} else {
			PreparedStatement psInsert = GeneralDB.getInstance().getConnection().prepareStatement("insert into " + Constants.COLOR_TABLENAME + " values (?, ?, ?, ?, ?, ?)");

			psInsert.setString(1, combination.getBowCathegory());
			psInsert.setString(2, combination.getAgeClass());
			psInsert.setString(3, combination.getGender());
			psInsert.setInt(4, toInsert.getRed());
			psInsert.setInt(5, toInsert.getGreen());
			psInsert.setInt(6, toInsert.getBlue());
			psInsert.executeUpdate();

			this.buffer.put(combination, toInsert);

			this.setChanged();
			this.notifyObservers(toInsert);
		}

		return true;
	}

	/**
	 * @param toUpdate
	 *            the Color to update in the DB
	 * @param objects
	 *            [0] the bow cathegory [1] the age class [3] the gender
	 */
	@Override
	public boolean update(Color toUpdate, Object... objects) throws SQLException {
		Combination combination = new Combination(objects [0].toString(), objects [1].toString(), objects [2].toString());

		if (this.buffer.get(combination).equals(toUpdate)) {
			return false;
		} else {
			PreparedStatement psUpdate = GeneralDB.getInstance().getConnection()
					.prepareStatement("update " + Constants.COLOR_TABLENAME + " set red=?, green=?, blue=? WHERE bowCathegory=? AND ageClass=? AND gender=?");

			psUpdate.setInt(1, toUpdate.getRed());
			psUpdate.setInt(2, toUpdate.getGreen());
			psUpdate.setInt(3, toUpdate.getBlue());
			psUpdate.setString(4, combination.getBowCathegory());
			psUpdate.setString(5, combination.getAgeClass());
			psUpdate.setString(6, combination.getGender());
			psUpdate.executeUpdate();

			this.buffer.put(combination, toUpdate);

			this.setChanged();
			this.notifyObservers(toUpdate);

			return true;
		}
	}

	/**
	 * @param toDelete
	 *            the Color to delete from the DB
	 * @param objects
	 *            [0] the bow cathegory [1] the age class [3] the gender
	 */
	@Override
	public boolean delete(Color toDelete, Object... objects) throws SQLException {
		Combination combination = new Combination(objects [0].toString(), objects [1].toString(), objects [2].toString());

		PreparedStatement psDelete = GeneralDB.getInstance().getConnection().prepareStatement("DELETE FROM " + Constants.COLOR_TABLENAME + " WHERE bowCathegory=? AND ageClass=? AND gender=?");

		psDelete.setString(1, combination.getBowCathegory());
		psDelete.setString(2, combination.getAgeClass());
		psDelete.setString(3, combination.getGender());
		psDelete.executeUpdate();

		GeneralDB.getInstance().getConnection().commit();

		this.buffer.remove(combination);

		this.setChanged();
		this.notifyObservers();

		return true;
	}

	/**
	 * @return a HashMap of all Entries saved in the DB
	 */
	@Override
	public HashMap < Combination, Color > getAll() {
		HashMap < Combination, Color > toReturn = new HashMap < Combination, Color >();
		Iterator < Combination > iterator = this.buffer.keySet().iterator();
		while (iterator.hasNext()) {
			Combination tmp = iterator.next();
			toReturn.put(tmp, this.buffer.get(tmp));
		}
		return toReturn;
	}

	/**
	 * @param key
	 *            [0] = bowCathegory key[1] = ageClass key[2] = gender
	 * @return the corresponding Color object
	 */
	@Override
	public Color getElement(Object... key) throws WrongNumberArgsException {
		Combination combination = new Combination(key [0].toString(), key [1].toString(), key [2].toString());
		if (this.buffer.containsKey(combination)) {
			return this.buffer.get(combination);
		} else {
			return new Color(255, 255, 255);
		}
	}

	/**
	 * creates the color table in the database of the form [bowCathegory,
	 * ageClass, gender, red, green, blue]
	 */
	@Override
	public void createTable() throws SQLException {
		try {
			GeneralDB.getInstance().getConnection().createStatement()
					.execute("create table " + Constants.COLOR_TABLENAME + "(bowCathegory varchar(40), ageClass varchar(40), gender varchar(40), red int, green int, blue int)");
		} catch (SQLException e) {
			// e.printStackTrace();
		}
	}

	/**
	 * drops the table from the database
	 */
	@Override
	public void dropTable() throws SQLException {
		GeneralDB.getInstance().getConnection().createStatement().execute("drop table " + Constants.COLOR_TABLENAME);
		instance = null;
	}

	/**
	 * fetches all colors saved in the DB and saves them in the local buffer
	 * @throws SQLException
	 */
	private void importAllColors() throws SQLException {
		this.buffer = new HashMap < Combination, Color >();
		ResultSet resultSet = GeneralDB.getInstance().getConnection().createStatement().executeQuery("SELECT * FROM " + Constants.COLOR_TABLENAME);
		while (resultSet.next()) {
			String bowCathegory = resultSet.getString(1);
			String ageClass = resultSet.getString(2);
			String gender = resultSet.getString(3);
			int red = resultSet.getInt(4);
			int green = resultSet.getInt(5);
			int blue = resultSet.getInt(6);

			this.buffer.put(new Combination(bowCathegory, ageClass, gender), new Color(red, green, blue));
		}
	}
}
